﻿Namespace Saglik.Evlenme
	Module Functions

		Function OdemeIstatistigi(ByVal Process As Token, ByVal Donem1 As String, ByVal Donem2 As String) As Recordset
			Q("select yasa,uyruk,bolge,sum(miktar) as miktar,count(adet) as adet from (")
			Q("select case when sno>10000000 then 'SGY' else 'SSY' end as yasa, case when kimlik between '000000' and '399999' and len(kimlik) <= 6 then '1' else '2' end as uyruk,")
			Q("sube as bolge,odememik as miktar, 1 as adet")
			Q("from ssdmerkez..sevlenme where convert(char(6),odemetar,112) between :~donem1 and :~donem2) t group by yasa,uyruk,bolge")
			Return Process.OpenSQL("*", Donem1, Donem2)
		End Function

		Function OdemeDonemleri() As String
			Return "q@select distinct convert(char(6),odemetar,112) as kod, convert(char(7),odemetar,111) as ad from ssdmerkez..sevlenme order by 1 desc"
		End Function

		Sub Sil(Token As Token, dosyano As String)
			Token.ExecSQL("delete from ssdmerkez.dbo.sevlenme where dosyano = @dosyano", dosyano)
		End Sub

		Function OkuKimlikle(Token As Token, kimlik As String) As Recordset
			Return Token.OpenSQL("select * from ssdmerkez.dbo.sevlenme where kimlik = @kimlik order by islemtar desc", kimlik)
		End Function

		Sub Kaydet(Token As Token, kimlik As String, sube As Integer, odememik As Decimal, dosyano As String)
			Dim Rs As Recordset = OkuKimlikle(Token, kimlik)
			Rs.SetValues("sube,odememik,dosyano", sube, odememik, dosyano)
			Rs.Save()
		End Sub

		Function OdenekListesi(Token As Token, order As Integer, yasa As Integer, raporturu As Integer, tarih1 As Date, tarih2 As Date, sube As Integer) As Recordset
			Dim orderBy As String = Choose(order + 1, "kimlik", "soyadi, adi", "islemtar", "murtar", "sube")
			Dim where As String = " where "
			Dim SS_SG_Kriter As String = ""
			Dim selectStr As String = ""
			'Sosyal Sigortaya Tabii veya Sosyal Güvenliğe Tabii olma Kriteri
			If yasa = 1 Then	' Sosyal Sigortaya Tabii
				SS_SG_Kriter = "  LEN(LTRIM(sno)) < 9 "
			ElseIf yasa = 2 Then	' Sosyal Güvenliğe Tabii
				SS_SG_Kriter = "  LEN(LTRIM(sno)) > 7 "
			ElseIf yasa = 0 Then	' Hepsi
				SS_SG_Kriter = " "
			End If
			If where.Trim.Length > 5 And SS_SG_Kriter.Trim.Length > 0 Then where = where & " and " & SS_SG_Kriter Else where = where & SS_SG_Kriter
			If where.Trim.Length > 5 Then where = where & " and "
			If raporturu = 0 Then
				selectStr = " Sno , kimlik , sube, adi ,  soyadi,  murtar,  islemtar , sevlentar, odememik  "
			Else
				selectStr = " Sno , kimlik , sube, adi ,  soyadi,  murtar,  islemtar "
			End If
			Dim EvlenmeSqlStr As String = "select " & selectStr & _
			  " from ssdmerkez..sevlenme " & where & _
			  " islemtar between :tarih1 and :tarih2 " & _
			  " and :sube in (0,sube) order by " & orderBy
			Return Token.OpenSQL(EvlenmeSqlStr, tarih1, tarih2, sube)
		End Function

	End Module
End Namespace
